from myproject.DataManage.DataBaseConfig import port,host,user,password,database
import pymysql
from pymysql import cursors

from myproject.config.config_reader import get_next_unfinished_target

# 从配置文件中读取表名
target_idx, target = get_next_unfinished_target()
TABLE_NAME=target["code"]


def create_table_if_not_exists(cursor, table_name, item):
    """
    根据 item 的键和值类型生成建表语句并执行
    """
    columns = ["id INT AUTO_INCREMENT PRIMARY KEY"]

    type_mapping = {
        str: "VARCHAR(255)",
        int: "INT",
        float: "DOUBLE",
        bool: "BOOLEAN"
    }

    for key, value in item.items():
        py_type = type(value)
        # 默认为 TEXT
        mysql_type = "VARCHAR(255)"
        if py_type in type_mapping:
            mysql_type = type_mapping[py_type]
        # 所有字符串类内容都用 TEXT，防止长度问题
        if isinstance(value, str):
            mysql_type = "VARCHAR(255)"
        columns.append(f"`{key}` {mysql_type} DEFAULT NULL")

    columns_def = ", ".join(columns)
    create_sql = f"""
    CREATE TABLE `{table_name}` (
        {columns_def}
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """
    try:
        cursor.execute(create_sql)
        print(f"✅ 表 `{table_name}` 创建成功")
        return True
    except Exception as e:
        print(f"⚠️ 建表失败: {e}")
        raise

def insert_item_to_mysql(item):#非常影响性能，用批量插入
    """
    接收 item 和 spider，插入数据库，表不存在则创建
    """
    connection = None
    cursor = None

    try:
        # 连接数据库
        connection = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=database,
            charset='utf8mb4',
            cursorclass=cursors.DictCursor
        )
        cursor = connection.cursor()

        # 尝试插入
        keys = [f"`{k}`" for k in item.keys()]
        values = [item[k] for k in item.keys()]
        placeholders = ", ".join(["%s"] * len(values))

        insert_sql = f"INSERT INTO `{TABLE_NAME}` ({', '.join(keys)}) VALUES ({placeholders})"

        try:
            cursor.execute(insert_sql, values)
            connection.commit()

        except pymysql.err.ProgrammingError as e:
            if "1146" in str(e) or "doesn't exist" in str(e).lower():
                # 建表
                create_table_if_not_exists(cursor, TABLE_NAME, item)
                connection.commit()
                # 重新插入
                cursor.execute(insert_sql, values)
                connection.commit()
            else:
                raise
        except Exception as e:
            connection.rollback()
            raise

    except Exception as e:
        raise
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()

def batch_insert_items_to_mysql(items):
    """
    批量插入 items 到 MySQL
    :param items: list of dict
    """
    if not items:
        return

    connection = None
    cursor = None

    try:
        connection = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=database,
            charset='utf8mb4',
            cursorclass=cursors.DictCursor
        )
        cursor = connection.cursor()

        # 提取字段名和值
        keys = [f"`{k}`" for k in items[0].keys()]
        placeholders = ", ".join(["%s"] * len(keys))
        insert_sql = f"INSERT INTO `{TABLE_NAME}` ({', '.join(keys)}) VALUES ({placeholders})"

        # 构建所有值的列表（用于 executemany）
        data = [[item[k] for k in items[0].keys()] for item in items]

        try:
            cursor.executemany(insert_sql, data)
            connection.commit()
            print(f"成功批量插入 {len(data)} 条记录")

        except pymysql.err.ProgrammingError as e:
            if "1146" in str(e) or "doesn't exist" in str(e).lower():
                # 表不存在，创建表
                create_table_if_not_exists(cursor, TABLE_NAME, items[0])
                connection.commit()
                # 重试插入
                cursor.executemany(insert_sql, data)
                connection.commit()
            else:
                raise
        except Exception as e:
            connection.rollback()
            raise

    except Exception as e:
        print(f"批量插入失败: {e}")
        raise
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


def get_max_current_page_num():
    """
    查询表中 current_page_num 字段的最大值。
    如果表不存在、无数据或字段不存在，返回 0。
    返回类型为 int。
    """
    connection = None
    try:
        # 建立数据库连接
        connection = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=database,
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )

        with connection.cursor() as cursor:
            # 检查表是否存在
            check_table_sql = """
                SELECT COUNT(*) AS count 
                FROM information_schema.tables 
                WHERE table_schema = %s 
                  AND table_name = %s
            """
            cursor.execute(check_table_sql, (database, TABLE_NAME))
            result = cursor.fetchone()

            if result['count'] == 0:
                print(f"表 {TABLE_NAME} 不存在")
                return 0

            # 检查字段是否存在
            check_column_sql = """
                SELECT COUNT(*) AS count 
                FROM information_schema.columns 
                WHERE table_schema = %s 
                  AND table_name = %s 
                  AND column_name = 'current_page_num'
            """
            cursor.execute(check_column_sql, (database, TABLE_NAME))
            column_result = cursor.fetchone()

            if column_result['count'] == 0:
                print(f"字段 'current_page_num' 在表 {TABLE_NAME} 中不存在")
                return 0

            # 查询 current_page_num 的最大值
            query_max_sql = f"SELECT MAX(current_page_num) AS max_page_num FROM `{TABLE_NAME}`"
            cursor.execute(query_max_sql)
            row = cursor.fetchone()

            # 返回最大值，如果为 None（表为空），则返回 0
            max_value = row['max_page_num']
            return int(max_value) if max_value is not None else 0

    except Exception as e:
        print(f"数据库查询出错: {e}")
        return 0

    finally:
        if connection:
            connection.close()